import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Scanner;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * @author: 成金乘
 * @date: 2022/11/22 11:46
 * @description:
 */
//数据库版本图书管理系统
public class JDBCBook {
    private static final DataSource datasource = new MysqlDataSource();
    static {
        ((MysqlDataSource)datasource).setURL("jdbc:mysql://127.0.0.1:3306/java106?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)datasource).setUser("root");
        ((MysqlDataSource)datasource).setPassword("111111");
    }
    public static void main(String[] args) {
        System.out.println(connection());
    }
    //获取数据库连接
    public static Connection connection(){
        try{
            return datasource.getConnection();
        }catch (SQLException e){
            throw new RuntimeException("数据库连接失败",e);
        }
    }
    //释放资源
    public static void close(ResultSet resultset, Statement statement, Connection connection){
        try{
            if(resultset != null){
                resultset.close();
            }
            if (statement != null){
                statement.close();
            }
            if (connection != null){
                connection.close();
            }
        }catch (SQLException e){
            throw new RuntimeException("资源释放异常",e);
        }
    }
}

//添加操作
class Add1{
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedstatement = null;
        try{
            connection = JDBCBook.connection();
            String sql = "insert into books(name,author) values(?,?)";
            preparedstatement = connection.prepareStatement(sql);
            preparedstatement.setString(1,"水浒传");
            preparedstatement.setString(2,"施耐庵");
            System.out.println(preparedstatement);
            //执行sql语句
            int row = preparedstatement.executeUpdate();
            System.out.println("影响了"+row+"行");
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCBook.close(null,preparedstatement,connection);
        }
    }
}

//修改操作
class UpDate{
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedstatement = null;
            //建立动态数据,通过控制台输入用户信息
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入书名 : ");
        String name = scanner.next();
        System.out.println("请输入书号 : ");
        int id = scanner.nextInt();

        try{
            connection = JDBCBook.connection();
            String sql = "update books set name = ? where id = ?";

            preparedstatement = connection.prepareStatement(sql);
            preparedstatement.setString(1,name);
            preparedstatement.setInt(2,id);
            System.out.println(preparedstatement);

            //执行sql语句
            int row = preparedstatement.executeUpdate();
            System.out.println("影响了"+row+"行");
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCBook.close(null,preparedstatement,connection);
        }
    }
}

//删除操作
class DelBooks{
    public static void main(String[] args) {
        //创建数据库连接
        Connection connection = null;
        //描述sql的对象
        PreparedStatement preparedstatement = null;

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入书号 : ");
        int id = scanner.nextInt();
        try {
            connection = JDBCBook.connection();
            String sql = "delete from books where id = ?";
            preparedstatement = connection.prepareStatement(sql);
            preparedstatement.setInt(1,id);
            System.out.println(preparedstatement);
            //执行sql语句
            int row = preparedstatement.executeUpdate();
            System.out.println("影响了"+row+"行");
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCBook.close(null,preparedstatement,connection);
        }
    }
}

//查询操作
class select1{
    public static void main(String[] args) {
        //创建数据库连接
        Connection connection = null;
        //描述sql的对象
        PreparedStatement preparedStatement = null;
        ResultSet resultset = null;
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入书号: ");
        int id = scanner.nextInt();
        try{
            connection = JDBCBook.connection();
            String sql = "select * from books where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            //执行语句
            System.out.println(preparedStatement);
            resultset = preparedStatement.executeQuery();
            while (resultset.next()){
                int Id = resultset.getInt("id");
                String name = resultset.getString("name");
                String author = resultset.getString("author");
                System.out.println("id:"+Id+" "+"name:"+name+" "+"author:"+author);
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            JDBCBook.close(resultset,preparedStatement,connection);
        }
    }
}